﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DDBuildTools.DataAccess.Components;

namespace DDBuildTools.DataAccess
{
    /// <summary>
    /// Mssql2000 数据库
    /// </summary>
    public class MsSql2000 : IDataBase
    {
        /// <summary>
        /// 获得表
        /// </summary>
        /// <param name="ConnStr"></param>
        /// <param name="DataBaseCode"></param>
        /// <returns></returns>
        public List<TableEntity> GetDataBaseTable(string ConnStr,out string DataBaseCode)
        {
            DataBaseCode = "";
            List<TableEntity> TableList = new List<TableEntity>();

            string SqlStr = "select   表名= d.name  ,  表说明=   isnull(f.value,''),DB_NAME() AS [DatabaseName]  from sysobjects d  left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0 where d.xtype='U'   and   d.status>=0 order by d.name";
            DataSet ds = SqlHelper.ExecuteDataset(ConnStr, CommandType.Text, SqlStr);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataBaseCode = ds.Tables[0].Rows[0]["DatabaseName"].ToString();

                foreach (DataRow var in ds.Tables[0].Rows)
                {
                    TableEntity Tb = new TableEntity();
                    Tb.DataBaseCode = var["DatabaseName"].ToString();
                    Tb.TableCode = var["表名"].ToString();
                    Tb.TableName = var["表说明"].ToString();

                    List<TableColume> TbColume = new List<TableColume>();

                    SqlStr = string.Format(@"SELECT     
                          表名= d.name  ,   
                          表说明=   isnull(f.value,'')   ,   
                          字段序号=a.colorder,   
                          字段名=a.name,   
                          标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   1 else  0   end,   
                          主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (   
                          SELECT   name   FROM   sysindexes   WHERE   indid   in(   
                          SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   
                          )))   then   1   else   0   end,   
                          类型=b.name,   
                          长度=a.prec,   
                          精度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
                          小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
                          允许空=case   when   a.isnullable=1   then   1 else   0   end,   
                          默认值=isnull(e.text,''),   
                          字段说明=isnull(g.[value],'')   

                          FROM   syscolumns   a   
                          left   join   systypes   b   on   a.xtype=b.xusertype   
                          inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and   d.status>=0   
                          left   join   syscomments   e   on   a.cdefault=e.id   
                          left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid       
                          left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0   

                          where   d.name='{0}'         --如果只查询指定表,加上此条件   
                          order   by   a.id,a.colorder   ", var["表名"]);
                    DataSet DsColume = SqlHelper.ExecuteDataset(ConnStr, CommandType.Text, SqlStr);
                    foreach (DataRow varColume in DsColume.Tables[0].Rows)
                    {
                        TableColume tl = new TableColume();
                        tl.Colume_Code = varColume["字段名"].ToString();
                        tl.Colume_DefaultValue = varColume["默认值"].ToString();
                        tl.Colume_Name = varColume["字段说明"].ToString();
                        tl.Colume_Type = varColume["类型"].ToString();
                        tl.Colume_Length = varColume["长度"].ToString();
                        tl.Colume_Scale = varColume["小数位数"].ToString();
                        tl.Colume_IsNullAble = Convert.ToBoolean(varColume["允许空"]);
                        tl.Colume_IsKey = Convert.ToBoolean(varColume["主键"]);
                        tl.Colume_Identity = Convert.ToBoolean(varColume["标识"]);
                        TbColume.Add(tl);
                    }

                    Tb.TableColume = TbColume;
                    TableList.Add(Tb);
                }
            }
            return TableList;
        }
    }
}
